using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Hematology.Data
{
	[DataObject]
	[Serializable]
	public partial class SiteUserExtended
	{
		#region Constants

		private const string TableName = "[dbo].[SiteUserExtended]";

		#endregion

		#region Properties

		public int? UserId { get; set; }
		public string FirstName { get; set; }
		public string MiddleName { get; set; }
		public string LastName { get; set; }
		public int? JobId { get; set; }
		public string JobPosition { get; set; }
		public DateTime? BirthDate { get; set; }
		public string Phone1 { get; set; }
		public string Phone2 { get; set; }
		public string Icq { get; set; }
		public string Skype { get; set; }

		#endregion

		#region Methods

		#region Mapping Methods

		protected void MapTo(DataSet ds)
		{
			if (ds == null)
				ds = new DataSet();

			if (ds.Tables[TableName] == null)
				ds.Tables.Add(TableName);

			ds.Tables[TableName].Columns.Add("UserId", typeof(Int32));
			ds.Tables[TableName].Columns.Add("FirstName", typeof(String));
			ds.Tables[TableName].Columns.Add("MiddleName", typeof(String));
			ds.Tables[TableName].Columns.Add("LastName", typeof(String));
			ds.Tables[TableName].Columns.Add("JobId", typeof(Int32));
			ds.Tables[TableName].Columns.Add("JobPosition", typeof(String));
			ds.Tables[TableName].Columns.Add("BirthDate", typeof(DateTime));
			ds.Tables[TableName].Columns.Add("Phone1", typeof(String));
			ds.Tables[TableName].Columns.Add("Phone2", typeof(String));
			ds.Tables[TableName].Columns.Add("ICQ", typeof(String));
			ds.Tables[TableName].Columns.Add("Skype", typeof(String));

			DataRow dr = ds.Tables[TableName].NewRow();

			dr["UserId"] = UserId ?? (object) DBNull.Value;
			dr["FirstName"] = FirstName ?? (object) DBNull.Value;
			dr["MiddleName"] = MiddleName ?? (object) DBNull.Value;
			dr["LastName"] = LastName ?? (object) DBNull.Value;
			dr["JobId"] = JobId ?? (object) DBNull.Value;
			dr["JobPosition"] = JobPosition ?? (object) DBNull.Value;
			dr["BirthDate"] = BirthDate ?? (object) DBNull.Value;
			dr["Phone1"] = Phone1 ?? (object) DBNull.Value;
			dr["Phone2"] = Phone2 ?? (object) DBNull.Value;
			dr["ICQ"] = Icq ?? (object) DBNull.Value;
			dr["Skype"] = Skype ?? (object) DBNull.Value;

			ds.Tables[TableName].Rows.Add(dr);
		}

		protected void MapFrom(DataRow dr)
		{
			UserId = dr["UserId"] != DBNull.Value ? Convert.ToInt32(dr["UserId"]) : UserId = null;
			FirstName = dr["FirstName"] != DBNull.Value ? Convert.ToString(dr["FirstName"]) : FirstName = null;
			MiddleName = dr["MiddleName"] != DBNull.Value ? Convert.ToString(dr["MiddleName"]) : MiddleName = null;
			LastName = dr["LastName"] != DBNull.Value ? Convert.ToString(dr["LastName"]) : LastName = null;
			JobId = dr["JobId"] != DBNull.Value ? Convert.ToInt32(dr["JobId"]) : JobId = null;
			JobPosition = dr["JobPosition"] != DBNull.Value ? Convert.ToString(dr["JobPosition"]) : JobPosition = null;
			BirthDate = dr["BirthDate"] != DBNull.Value ? Convert.ToDateTime(dr["BirthDate"]) : BirthDate = null;
			Phone1 = dr["Phone1"] != DBNull.Value ? Convert.ToString(dr["Phone1"]) : Phone1 = null;
			Phone2 = dr["Phone2"] != DBNull.Value ? Convert.ToString(dr["Phone2"]) : Phone2 = null;
			Icq = dr["ICQ"] != DBNull.Value ? Convert.ToString(dr["ICQ"]) : Icq = null;
			Skype = dr["Skype"] != DBNull.Value ? Convert.ToString(dr["Skype"]) : Skype = null;

			FillUserBase();
		}

		public static List<SiteUserExtended> MapFrom(DataSet ds)
		{
			// Initialise Collection.
			List<SiteUserExtended> objects = new List<SiteUserExtended>();

			// Validation.
			if (ds == null)
				throw new ApplicationException("Cannot map to dataset null.");
			
			if (ds.Tables[TableName].Rows.Count == 0)
				return objects;

			if (ds.Tables[TableName] == null)
				throw new ApplicationException("Cannot find table [dbo].[SiteUserExtended] in DataSet.");

			if (ds.Tables[TableName].Rows.Count < 1)
				throw new ApplicationException("Table [dbo].[SiteUserExtended] is empty.");

			// Map DataSet to Instance.
			foreach (DataRow dr in ds.Tables[TableName].Rows)
			{
				SiteUserExtended instance = new SiteUserExtended();
				instance.MapFrom(dr);
				instance.FillUserBase();
				objects.Add(instance);
			}

			// Return collection.
			return objects;
		}

		#endregion

		#region CRUD Methods

		public static SiteUserExtended Get(Int32 userId)
		{
			SiteUserExtended instance = new SiteUserExtended();

			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUserExtended_SELECT";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, userId))
			{
				using (DataSet ds = db.ExecuteDataSet(dbCommand))
				{
					if (ds == null || ds.Tables[0].Rows.Count == 0) throw new ApplicationException(String.Format("Could not get SiteUserExtended ID:{0} from Database.", userId));
					ds.Tables[0].TableName = TableName;

					instance.MapFrom(ds.Tables[0].Rows[0]);
				}
			}
			return instance;
		}

		#region INSERT

		private void Insert(Int32? userId, String firstName, String middleName, String lastName, Int32? jobId, String jobPosition, DateTime? birthDate, String phone1, String phone2, String icq, String skype, DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUserExtended_INSERT";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, userId, firstName, middleName, lastName, jobId, jobPosition, birthDate, phone1, phone2, icq, skype))
			{
				if (transaction == null)
					db.ExecuteScalar(dbCommand);
				else
					db.ExecuteScalar(dbCommand, transaction);
			}
		}

		/// <summary>
		/// Insert current SiteUserExtended to database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Insert(DbTransaction transaction = null)
		{
			Insert(UserId, FirstName, MiddleName, LastName, JobId, JobPosition, BirthDate, Phone1, Phone2, Icq, Skype, transaction);
		}

		#endregion

		#region UPDATE

		public static void Update(Int32? userId, String firstName, String middleName, String lastName, Int32? jobId, String jobPosition, DateTime? birthDate, String phone1, String phone2, String icq, String skype, DbTransaction transaction)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUserExtended_UPDATE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
			{
				db.DiscoverParameters(dbCommand);

				dbCommand.Parameters["@userId"].Value = userId;
				dbCommand.Parameters["@firstName"].Value = firstName;
				dbCommand.Parameters["@middleName"].Value = middleName;
				dbCommand.Parameters["@lastName"].Value = lastName;
				dbCommand.Parameters["@jobId"].Value = jobId;
				dbCommand.Parameters["@jobPosition"].Value = jobPosition;
				dbCommand.Parameters["@birthDate"].Value = birthDate;
				dbCommand.Parameters["@phone1"].Value = phone1;
				dbCommand.Parameters["@phone2"].Value = phone2;
				dbCommand.Parameters["@icq"].Value = icq;
				dbCommand.Parameters["@skype"].Value = skype;

				if (transaction == null)
					db.ExecuteNonQuery(dbCommand);
				else
					db.ExecuteNonQuery(dbCommand, transaction);
			}
		}

		public static void Update(Int32? userId, String firstName, String middleName, String lastName, Int32? jobId, String jobPosition, DateTime? birthDate, String phone1, String phone2, String icq, String skype)
		{
			Update(userId, firstName, middleName, lastName, jobId, jobPosition, birthDate, phone1, phone2, icq, skype, null);
		}

		public static void Update(SiteUserExtended siteUserExtended)
		{
			siteUserExtended.Update();
		}

		public static void Update(SiteUserExtended siteUserExtended, DbTransaction transaction)
		{
			siteUserExtended.Update(transaction);
		}

		/// <summary>
		/// Updates changes to the database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		public void Update(DbTransaction transaction)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUserExtended_UPDATE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
			{
				db.DiscoverParameters(dbCommand);

				dbCommand.Parameters["@userId"].SourceColumn = "UserId";
				dbCommand.Parameters["@firstName"].SourceColumn = "FirstName";
				dbCommand.Parameters["@middleName"].SourceColumn = "MiddleName";
				dbCommand.Parameters["@lastName"].SourceColumn = "LastName";
				dbCommand.Parameters["@jobId"].SourceColumn = "JobId";
				dbCommand.Parameters["@jobPosition"].SourceColumn = "JobPosition";
				dbCommand.Parameters["@birthDate"].SourceColumn = "BirthDate";
				dbCommand.Parameters["@phone1"].SourceColumn = "Phone1";
				dbCommand.Parameters["@phone2"].SourceColumn = "Phone2";
				dbCommand.Parameters["@icq"].SourceColumn = "ICQ";
				dbCommand.Parameters["@skype"].SourceColumn = "Skype";

				using (DataSet ds = new DataSet())
				{
					MapTo(ds);
					ds.AcceptChanges();
					ds.Tables[0].Rows[0].SetModified();

					if (transaction == null)
						db.UpdateDataSet(ds, TableName, null, dbCommand, null, UpdateBehavior.Standard);
					else
						db.UpdateDataSet(ds, TableName, null, dbCommand, null, transaction);
				}
			}
		}

		/// <summary>
		/// Updates changes to the database.
		/// </summary>
		public void Update()
		{
			Update((DbTransaction)null);
		}
		
		#endregion

		#region DELETE

		private static void Delete(Int32? userId, DbTransaction transaction)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUserExtended_DELETE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, userId))
			{
				if (transaction != null)
				{
					db.ExecuteNonQuery(dbCommand, transaction);
				}
				else
				{
					db.ExecuteNonQuery(dbCommand);
				}
			}
		}

		private static void Delete(Int32? userId)
		{
			Delete(
				userId);
		}

		/// <summary>
		/// Delete current SiteUserExtended from database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Delete(DbTransaction transaction)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUserExtended_DELETE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, UserId))
			{
				if (transaction != null)
				{
					db.ExecuteNonQuery(dbCommand, transaction);
				}
				else
				{
					db.ExecuteNonQuery(dbCommand);
				}
			}
		}

		/// <summary>
		/// Delete current SiteUserExtended from database.
		/// </summary>
		private void Delete()
		{
			Delete((DbTransaction)null);
		}

		#endregion

		#region SEARCH

		private static List<SiteUserExtended> Search(Int32? userId = null, String firstName = null, String middleName = null, String lastName = null, Int32? jobId = null, String jobPosition = null, DateTime? birthDate = null, String phone1 = null, String phone2 = null, String icq = null, String skype = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUserExtended_SEARCH";
			List<SiteUserExtended> siteUsers;
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, userId, firstName, middleName, lastName, jobId, jobPosition, birthDate, phone1, phone2, icq, skype))
			{
				using (DataSet ds = db.ExecuteDataSet(dbCommand))
				{
					ds.Tables[0].TableName = TableName;
					siteUsers = MapFrom(ds);
				}
			}
			if (siteUsers != null)
			{
				siteUsers.Sort();
			}

			return siteUsers;
		}

		private static List<SiteUserExtended> Search(SiteUserExtended searchObject)
		{
			return Search(searchObject.UserId, searchObject.FirstName, searchObject.MiddleName, searchObject.LastName, searchObject.JobId, searchObject.JobPosition, searchObject.BirthDate, searchObject.Phone1, searchObject.Phone2, searchObject.Icq, searchObject.Skype);
		}

		#endregion

		#endregion

		#endregion
	}
}